﻿using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Helpers;
using System.Web.Mvc;
using System.Web.Script.Serialization;
using Microsoft.Office.Interop.Word;
using QBWeb.Models;
using Microsoft.AspNet.Identity;
using QBWeb.Models.CurriculumFolder;

namespace QBWeb.BLL
{
    public static class CurriculumBll
    {
        /// <summary>
        /// 数据库
        /// </summary>
        private static readonly QBDbContext db = new QBDbContext();
        /// <summary>
        /// 增加课程
        /// </summary>
        /// <param name="curriName"></param>
        /// <param name="year"></param>
        /// <param name="semester"></param>
        /// <param name="userId"></param>
        public static void AddCurriculum(string curriName, string year, string semester,string userId)
        {
            if (curriName == "") throw new Exception("课程名不能为空");
            var findRes = db.Curricula.FirstOrDefault(a => a.CurriName == curriName); //暂时缺少教师信息
            if (findRes != null) throw new Exception("课程名重复");
            var curriculum = new Curriculum
            {
                CurriCode = CodeNum(),
                CurriName = curriName,
                Years = year,
                Semester = semester,
                TeacherId = userId,
                Menbers = 0
            };
            db.Curricula.Add(curriculum);
            db.SaveChanges();

        }
        /// <summary>
        /// 编辑课程
        /// </summary>
        /// <param name="curriName"></param>
        /// <param name="year"></param>
        /// <param name="semester"></param>
        /// <param name="Id"></param>
        public static void EditCurriculum(string curriName, string year, string semester, int Id)
        {
            object[] param = {curriName, year, semester, Id};
            //var findRes = db.Curricula.Where(a => a.CurriName == curriName).ToList(); //暂时缺少教师信息
           // if (findRes.Count()>1) throw new Exception("课程名重复");
            var sql = "Update Curricula Set CurriName=@P0,Years=@P1,Semester=@P2 WHERE Id=@P3"; 
            db.Database.ExecuteSqlCommand(sql, param);
        }
        /// <summary>
        /// 删除课程
        /// </summary>
        /// <param name="curriculumId"></param>
        public static void DeleteCurriculum(int curriculumId)
        {
            var sql = "";
            var findRes = db.Curricula.Find(curriculumId);
            if (findRes == null) throw new Exception("未找到课程信息");
            var subject = db.SubjectTbs.Where(a => a.CurriculaId == curriculumId).ToList();
            if (subject.Any())
            {
                foreach (var item in subject)
                {
                    var bigModule = db.BigKnowModuleTbs.Where(b => b.SubjectId == item.Id).ToList();
                    if (bigModule.Any())
                    {
                        foreach (var i in bigModule)
                        {
                            var smallModule = db.SmallKnowModuleTbs.Where(c => c.BigKnowModuleTbId == i.Id).ToList();
                            if (smallModule.Any())
                            {
                                sql = "delete from SmallKnowModuleTbs where BigKnowModuleTbId=@P0";
                                object[] para1 = {i.Id};
                                CommonDelete(sql, para1);
                            }
                        }

                        sql = "delete from BigKnowModuleTbs where SubjectId=@P0";
                        object[] para2 = {item.Id};
                        CommonDelete(sql, para2);
                    }
                }

                sql = "delete from SubjectTbs where CurriculaId=@P0";
                object[] para3 = {curriculumId};
                CommonDelete(sql, para3);
            }

            var classTb = db.ClassesTbs.Where(a => a.CurriculumId == curriculumId).ToList();
            if (classTb.Any())
            {
                sql = "delete from ClassesTbs where CurriculumId=@P0";
                object[] para4 = {curriculumId};
                CommonDelete(sql, para4);
            }

            sql = "delete from Curricula where Id=@P0";
            object[] para5 = {curriculumId};
            CommonDelete(sql, para5);
        }
        /// <summary>
        /// 删除函数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        private static void CommonDelete(string sql, object[] parameter)
        {
            db.Database.ExecuteSqlCommand(sql, parameter);
        }

        #region 题目分类管理
        /// <summary>
        /// 手动修改题目类别
        /// </summary>
        /// <param name="id"></param>
        /// <param name="classData"></param>
        public static void ModifyQuestClass(int id, string classData)
        {
            var dic = JsonToDictionary(classData);
            foreach (var item in dic)
            { 
                var quest=db.QuestionTbs.Find(int.Parse(item.Key));
                if (int.Parse(item.Value) == 1)
                {
                    quest.SmallKnowModuleTbId = id;
                    db.Entry(quest).State = EntityState.Modified;
                }
                else
                {
                    quest.SmallKnowModuleTbId = null;
                    db.Entry(quest).State = EntityState.Modified;
                }
            }
            db.SaveChanges();
        }
        /// <summary>
        /// 反序列化工具
        /// </summary>
        /// <param name="jsonData"></param>
        /// <returns></returns>
        private static Dictionary<string, string> JsonToDictionary(string jsonData)
        {
            //实例化JavaScriptSerializer类的新实例
            JavaScriptSerializer jss = new JavaScriptSerializer();
            try
            {
                //将指定的 JSON 字符串转换为 Dictionary<string,string> 类型的对象
                return jss.Deserialize<Dictionary<string, string>>(jsonData);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion

        #region 加课管理
        public static void JoinCurricula(string studentId, int curriculumId, string className)
        {
            
            //查询是否有重复加入课程
            var temp = db.CurriStudentTbs.Where(a => a.CurriculumId == curriculumId&&a.StudentId==studentId).ToList();
            if (temp.Any())
            {
               throw new Exception("您已经加过了");
            }

            var curriStudent = new Curri_StudentTb()
            {
                CurriculumId = curriculumId,
                StudentId = studentId
            };
            var classId = db.ClassesTbs.FirstOrDefault(a => a.ClassName == className&&a.CurriculumId==curriculumId)?.Id;
            if(classId==null) throw new Exception("加课失败");

            var classStudent = new Classes_StudentTb()
            {
                StudentId = studentId,
                ClassesTbId =Convert.ToInt32(classId) 
            };
            db.ClassesStudentTbs.Add(classStudent);
            db.CurriStudentTbs.Add(curriStudent);
            var curriculum = db.Curricula.Find(curriStudent.CurriculumId);
            var classes = db.ClassesTbs.Find(classId);
            if (curriculum != null)
            {
                curriculum.Menbers += 1;
                classes.InMenbers += 1;
                db.Entry(curriculum).State = EntityState.Modified;
                db.Entry(classes).State = EntityState.Modified;
                db.SaveChanges();
            }
        }
        
        public static void QuitCurricula([Bind(Include = "CurriculumId,StudentId")] Curri_StudentTb curriStudent)
        {
            if (curriStudent == null) throw new Exception("退出失败");
            object[] param = {curriStudent.CurriculumId, curriStudent.StudentId};
            const string sql = "DELETE FROM Curri_StudentTb WHERE CurriculumId=@P0 AND StudentId=@P1";
            const string sql2 = "SELECT A.Id,A.ClassesTbId,A.StudentId FROM Classes_StudentTb A JOIN ClassesTbs B ON A.ClassesTbId=B.Id " +
                                "WHERE B.CurriculumId=@P0 AND A.StudentId=@P1";
            const string sql3 = "DELETE FROM Classes_StudentTb WHERE Id=@P0";
            var classId = db.Database.SqlQuery<Classes_StudentTb>(sql2, param);//联合查询订阅表ID
            //var sum = db.ClassesTbs.Where(a => a.CurriculumId == curriStudent.CurriculumId).Sum(a => a.InMenbers);
            object[] param2 = {classId.First().Id};
            var curriculum = db.Curricula.Find(curriStudent.CurriculumId);
            var classEf = db.ClassesTbs.Find(classId.First().ClassesTbId);
            if (classEf == null) throw new Exception("退出失败");
            if (curriculum == null) throw new Exception("退出失败");
            classEf.InMenbers -= 1;
            curriculum.Menbers -= 1;
            db.Entry(classEf).State = EntityState.Modified;
            db.Entry(curriculum).State = EntityState.Modified;
            db.SaveChanges();
            db.Database.ExecuteSqlCommand(sql3, param2);  //删除班级订阅表
            db.Database.ExecuteSqlCommand(sql, param); //删除课程订阅表


        }


        #endregion
        /// <summary>
        /// 生成课程代码
        /// </summary>
        /// <returns></returns>
        private static string CodeNum()
        {
            var ran = new Random();
            var starts = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,0,1,2,3,4,5,6,7,8,9".Split(',');
            var index = ran.Next(0, starts.Length - 10);
            var first = starts[index];
            index = ran.Next(0, starts.Length - 10);
            var second = starts[index];
            index = ran.Next(0, starts.Length - 1);
            var three = starts[index];
            index = ran.Next(0, starts.Length - 10);
            var four = starts[index];
            index = ran.Next(0, starts.Length - 1);
            var last = starts[index];
            var newCode = first + second + three + four + last;
            return newCode;
        }
        /// <summary>
        /// 添加班级
        /// </summary>
        /// <param name="curriculumId"></param>
        /// <param name="className"></param>
        public static void AddClasses(int curriculumId, string className)
        {
            var classes = new ClassesTb()
            {
                CurriculumId = curriculumId,
                ClassName = className,
                InMenbers = 0
            };
            var finds = db.ClassesTbs.Where(b => (b.CurriculumId == curriculumId) && (b.ClassName == className));
            if (finds.Any()) throw new Exception("班级名重复！");
            db.ClassesTbs.Add(classes);
            db.SaveChanges();
        }
        /// <summary>
        /// 编辑编辑
        /// </summary>
        /// <param name="curriculumId"></param>
        /// <param name="newName"></param>
        /// <param name="oldName"></param>
        public static void EditClasses(int curriculumId, string newName, string oldName)
        {
            if (newName == oldName) return;
            var finds = db.ClassesTbs.Where(b => (b.CurriculumId == curriculumId) && (b.ClassName == oldName));
            if (!finds.Any()) throw new Exception("未找到对应的班级信息！");
            var name = db.ClassesTbs.Where(b => (b.CurriculumId == curriculumId) && (b.ClassName == newName));
            if (name.Any()) throw new Exception("班级名重复！");
            object[] param = {newName, curriculumId, oldName};
            const string sql = "Update ClassesTbs Set ClassName=@P0 WHERE CurriculumId=@P1 and ClassName=@P2";
            db.Database.ExecuteSqlCommand(sql, param);
        }
        /// <summary>
        /// 添加知识模块
        /// </summary>
        /// <param name="newTitle"></param>
        /// <param name="id"></param>
        /// <param name="rank"></param>
        /// <param name="cid"></param>
        /// <returns></returns>
        public static List<TempList> AddModule(string newTitle, int id, int rank,int cid)
        {
            var re = new List<TempList>();
            var tempList = new TempList();
            
            switch (rank)
            {
                case 1:
                    if (id == -1) //-1为根目录，其它为二级
                    {
                        var res = db.SubjectTbs.FirstOrDefault(a => a.CurriculaId == cid && a.SubjectName == newTitle);
                        if (res != null)
                        {
                            tempList.Msg = false;
                            tempList.RootOrNot=true;
                            re.Add(tempList);
                            return re;
                        } //判断重名
                        var subjectTb = new SubjectTb()
                        {
                            SubjectName = newTitle,
                            CurriculaId = cid
                        };
                        db.SubjectTbs.Add(subjectTb);
                        db.SaveChanges();
                        tempList.Msg = true;
                        re.Add(tempList);
                        return re;
                    }
                    else
                    {
                        var res = db.BigKnowModuleTbs.FirstOrDefault(a => a.SubjectId == id && a.BigModuleName == newTitle);
                        if (res != null)
                        {
                            tempList.Msg = false;
                            re.Add(tempList);
                            return re;
                        }
                        var bigModule = new BigKnowModuleTb()
                        {
                            SubjectId = id,
                            BigModuleName = newTitle
                        };
                        db.BigKnowModuleTbs.Add(bigModule);
                        db.SaveChanges();
                        var tempList2 = new TempList()
                        {
                            Msg = true,
                            Id = bigModule.Id,
                            Title = newTitle,
                            VaryId = bigModule.SubjectId,
                            Rank = rank + 1
                        };
                        re.Add(tempList2);
                        return re;
                    }

                case 2:
                    var res2 = db.SmallKnowModuleTbs.FirstOrDefault(a => a.BigKnowModuleTbId == id && a.SmallModuleName == newTitle);
                    if (res2 != null)
                    {
                        tempList.Msg = false;
                        re.Add(tempList);
                        return re;
                    }
                    var small = new SmallKnowModuleTb()
                    {
                        BigKnowModuleTbId = id,
                        SmallModuleName = newTitle,
                        CurriculumId= cid,
                        Keywords = newTitle
                    };
                    db.SmallKnowModuleTbs.Add(small);
                    db.SaveChanges();
                    var tempList3 = new TempList()
                    {
                        Msg = true,
                        Id = small.Id,
                        Title = newTitle,
                        VaryId = small.BigKnowModuleTbId,
                        Rank = rank + 1
                    };
                    re.Add(tempList3);
                    return re;
            }
            tempList.Msg = false;
            re.Add(tempList);
            return re;

        }
        /// <summary>
        /// 删除班级
        /// </summary>
        /// <param name="curriculumId"></param>
        /// <param name="className"></param>
        public static void DeleteClasses(int curriculumId, string className)
        {
            var finds = db.ClassesTbs.Where(b => (b.CurriculumId == curriculumId) && (b.ClassName == className));
            if (!finds.Any()) throw new Exception("班级名不存在");
            object[] param = {curriculumId, className};
            const string sql = "Delete From ClassesTbs WHERE CurriculumId=@P0 and ClassName=@P1";
            db.Database.ExecuteSqlCommand(sql, param);
        }
        /// <summary>
        /// 编辑知识模块
        /// </summary>
        /// <param name="newTitle"></param>
        /// <param name="varyId"></param>
        /// <param name="id"></param>
        /// <param name="rank"></param>
        public static void EditModule(string newTitle, int varyId, int id, int rank)
        {
            switch (rank)
            {
                case 1:
                    var res1 = db.SubjectTbs.FirstOrDefault(a => a.Id == id && a.CurriculaId == varyId);
                    if (res1 == null) throw new Exception( "未找到数据" );
                    if (newTitle == res1.SubjectName) throw new Exception("false" );
                    var distinctName = db.SubjectTbs.Where(a => a.CurriculaId == varyId && a.SubjectName == newTitle);
                   
                    if (distinctName.Any()) throw new Exception("exist");
                    res1.SubjectName = newTitle;
                    db.Entry(res1).State = EntityState.Modified;
                    db.SaveChanges();
                    break;
                case 2:
                    var res2 = db.BigKnowModuleTbs.FirstOrDefault(a => a.Id == id && a.SubjectId == varyId);
                    if (res2 == null) throw new Exception("未找到数据");
                    if (newTitle == res2.BigModuleName) throw new Exception("false");
                    var distinctName2 = db.BigKnowModuleTbs.Where(a => a.SubjectId == varyId && a.BigModuleName == newTitle);
                  
                    if (distinctName2.Any()) throw new Exception("exist");
                    res2.BigModuleName = newTitle;
                    db.Entry(res2).State = EntityState.Modified;
                    db.SaveChanges();
                    break;
                case 3:
                    var res3 = db.SmallKnowModuleTbs.FirstOrDefault(a => a.Id == id && a.BigKnowModuleTbId == varyId);
                    if (res3 == null) throw new Exception("未找到数据");
                    if (newTitle == res3.SmallModuleName) throw new Exception("false");
                    var distinctName3 = db.SmallKnowModuleTbs.Where(a => a.BigKnowModuleTbId == varyId && a.SmallModuleName== newTitle);
                    
                    if (distinctName3.Any()) throw new Exception("exist");
                    res3.SmallModuleName = newTitle;
                    db.Entry(res3).State = EntityState.Modified;
                    db.SaveChanges();
                    break;
            }
        }


        /// <summary>
        /// 删除知识模块
        /// </summary>
        /// <param name="id"></param>
        /// <param name="rank"></param>
        /// <returns></returns>
        public static bool DeleteModule(int id, int rank)
        {
            var sql = "";
            //目前无法开启数据库级联删除
            switch (rank)
            {
                case 1:
                    var subjectTb = db.SubjectTbs.Find(id);
                    if (subjectTb != null)
                    {
                        var big = db.BigKnowModuleTbs.Where(a => a.SubjectId == subjectTb.Id).ToList();
                        if (big.Any())
                        {
                            foreach (var i in big)
                            {
                                var small = db.SmallKnowModuleTbs.Where(a => a.BigKnowModuleTbId == i.Id).ToList();
                                if (small.Any())
                                {
                                    sql = "delete from SmallKnowModuleTbs where BigKnowModuleTbId=@P0";
                                    object[] para1 = {i.Id };
                                    db.Database.ExecuteSqlCommand(sql, para1);
                                }
                            }
                            sql = "delete from BigKnowModuleTbs where SubjectId=@P0";
                            object[] para2 = { subjectTb.Id };
                            db.Database.ExecuteSqlCommand(sql, para2);
                        }
                        //db.SubjectTbs.Remove(subjectTb);  
                        //db.SaveChanges();有bug 不适用
                        sql = "delete from SubjectTbs where Id=@P0";
                        object[] para0 = { id };
                        db.Database.ExecuteSqlCommand(sql, para0);
                        return true;
                    }
                    break;
                case 2:
                    var big2 = db.BigKnowModuleTbs.Find(id);
                    if (big2 != null)
                    {
                        var small = db.SmallKnowModuleTbs.Where(a => a.BigKnowModuleTbId == big2.Id);
                        if (small.Any())
                        {
                            sql = "delete from SmallKnowModuleTbs where BigKnowModuleTbId=@P0";
                            object[] para = { big2.Id };
                            db.Database.ExecuteSqlCommand(sql, para);
                        }
                        db.BigKnowModuleTbs.Remove(big2);  //需要try catch
                        db.SaveChanges();
                        return true;
                    }
                    break;
                case 3:
                    var small2 = db.SmallKnowModuleTbs.Find(id);
                    if (small2 != null)
                    {
                        db.SmallKnowModuleTbs.Remove(small2);
                        db.SaveChanges();
                        return true;
                    }
                    break;
            }
            return false;
        }

    }

    //临时泛型
    public  class TempList
    {
        public bool Msg { get; set; }
        //判断是不是根节点
        public bool RootOrNot { get; set; }
        public int Id{ get; set; }
        public string Title { get; set; }
        public int VaryId { get; set; }
        public int Rank { get; set; }
    }
}